package com.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.Border;
import jxl.write.BorderLineStyle;
import jxl.write.Formula;
import jxl.write.Label;
import jxl.write.NumberFormat;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.apache.log4j.Logger;
import org.apache.struts.action.ActionForm;

import com.dao.common.DbOperationExt;

public class ExcelExport{

	static Logger log = Logger.getLogger(ExcelExport.class);

	/**
	 * 文件输出
	 * 
	 * @param String fileName   文件信息名称(无扩展名)
	 * @param List titleList   标题
	 * @param List dataList 
	 * @param List keyList  
	 * @param HttpServletResponse response
	 * @param HttpServletResponse response
	 */
	public static void export(String fileName, List titleList, List dataList, List keyList, HttpServletResponse response)
			throws Exception {
		Map dataInfo = null;
		File file = null;
		try {
			/* 构建文件 */
			file = new File(fileName + ".xls");
			
			/* 构建Workbook对象 */
			WritableWorkbook wwb = Workbook.createWorkbook(file);

			/* 创建sheet对象 */
			WritableSheet ws = wwb.createSheet(fileName, 0);

			/* 设定col列宽 */
			setExcelColWith(ws, titleList);

			/* 数据title写入 */
			setExcelTitleData(ws, 0, titleList);

			/* 数据写入 */
			for (int iRow = 0; iRow < dataList.size(); iRow++) {
				/* 信息取得 */
				dataInfo = (Map) dataList.get(iRow);
				/* 添加Label对象 */
				setExcelData(ws, iRow + 1, dataInfo, keyList);
			}
			
			/* 写入Excel工作薄 */
			wwb.write();

			/* 关闭Excel工作薄对象 */
			wwb.close();
			
			/* 文件下载 */
			fileUpload(fileName + ".xls", file, response);
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	/**
	 * 文件输出
	 * 
	 * @param String fileName   文件信息名称(无扩展名)
	 * @param List titleList   标题
	 * @param String sql ActionForm  sql文


	 * @param HttpServletResponse response
	 */
	 public static void exportTotalOther(String fileName, List titleList,List totalList, String sql,ActionForm actionForm, HttpServletResponse response)
		throws Exception {
	DbOperationExt dao = new DbOperationExt();
	List list = new ArrayList();
	List colList = new ArrayList();
	File file = null;
	try {
		file = new File(fileName + ".xls");
		list = dao.queryDataTotalOther(sql,actionForm,totalList);
		WritableWorkbook wwb = Workbook.createWorkbook(file);
		WritableSheet ws = wwb.createSheet(fileName, 0);

		setExcelColWith(ws, titleList);

		setExcelTitleData(ws, 0, titleList);

		for (int iRow = 0; iRow < list.size(); iRow++) {
			
			colList = (List)list.get(iRow);
			
			setExcelData(ws, iRow + 1, colList);
		}
	
		wwb.write();

		wwb.close();
		
		fileUpload(fileName + ".xls", file, response);
		
	} catch (Exception e) {
		e.printStackTrace();
	} finally {
		if (dao != null)
			dao.disConnect();
	}
}


	/**
	 * 文件输出
	 * 
	 * @param String fileName   文件信息名称(无扩展名)
	 * @param List titleList   标题
	 * @param String sql String sql1  sql文


	 * @param HttpServletResponse response
	 */
	public static void exportTotal(String fileName, List titleList, String sql,String sqlTotal, HttpServletResponse response)
			throws Exception {
		DbOperationExt dao = new DbOperationExt();
		List list = new ArrayList();
		List colList = new ArrayList();
		File file = null;
		try {
			/* 构建文件 */
			file = new File(fileName + ".xls");
			
			/* 指定的数据查询 */
			list = dao.queryDataTotal(sql,sqlTotal);
			/* 构建Workbook对象 */
			WritableWorkbook wwb = Workbook.createWorkbook(file);

			/* 创建sheet对象 */
			WritableSheet ws = wwb.createSheet(fileName, 0);

			/* 设定col列宽 */
			setExcelColWith(ws, titleList);

			/* 数据title写入 */
			setExcelTitleData(ws, 0, titleList);

			/* 数据写入 */
			for (int iRow = 0; iRow < list.size(); iRow++) {
				/* 信息取得 */
				colList = (List)list.get(iRow);
				/* 添加Label对象 */
				setExcelData(ws, iRow + 1, colList);
			}
			/* 写入Excel工作薄 */
			wwb.write();

			/* 关闭Excel工作薄对象 */
			wwb.close();
			
			/* 文件下载 */
			fileUpload(fileName + ".xls", file, response);
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (dao != null)
				dao.disConnect();
		}
	}
	/**
	 * 文件输出
	 * 
	 * @param String fileName   文件信息名称(无扩展名)
	 * @param List titleList   标题
	 * @param String sql   sql文

	 * @param HttpServletResponse response
	 */
	public static void export(String fileName, List titleList, String sql, HttpServletResponse response)
			throws Exception {
		DbOperationExt dao = new DbOperationExt();
		List list = new ArrayList();
		List colList = new ArrayList();
		File file = null;
		try {
			/* 构建文件 */
			file = new File(fileName + ".xls");

			/* 指定的数据查询 */
			list = dao.queryData(sql);
			/* 构建Workbook对象 */
			WritableWorkbook wwb = Workbook.createWorkbook(file);

			/* 创建sheet对象 */
			WritableSheet ws = wwb.createSheet(fileName, 0);

			/* 设定col列宽 */
			setExcelColWith(ws, titleList);

			/* 数据title写入 */
			setExcelTitleData(ws, 0, titleList);

			/* 数据写入 */
			for (int iRow = 0; iRow < list.size(); iRow++) {
				/* 信息取得 */
				colList = (List)list.get(iRow);
				/* 添加Label对象 */
				setExcelData(ws, iRow + 1, colList);
			}
			/* 写入Excel工作薄 */
			wwb.write();

			/* 关闭Excel工作薄对象 */
			wwb.close();
			
			/* 文件下载 */
			fileUpload(fileName + ".xls", file, response);
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (dao != null)
				dao.disConnect();
		}
	}

//	/**
//	 * 文件输出
//	 * wangk071226  先调用存储过程，然后执行SQL文 用于动态表的EXCEL导出
//	 * @param String fileName   文件信息名称(无扩展名)
//	 * @param List titleList   标题
//	 * @param String sql   sql文

//	 * @param HttpServletResponse response
//	 */
//	public static void exportCall(String fileName, List titleList, String sql,String str,HttpServletResponse response,String para)
//			throws Exception {
//		DbOperationExt dao = new DbOperationExt();
//		List list = new ArrayList();
//		List colList = new ArrayList();
//		File file = null;
//		try {
//			/* 构建文件 */
//			file = new File(fileName + ".xls");
//
//			/* 指定的数据查询 */
//			list = dao.queryDataCallWithParm(sql,str,para);
//			if(!"".equals(para)&&list.size()!=0){
//				list.remove(0);
//			}
//			/* 构建Workbook对象 */
//			WritableWorkbook wwb = Workbook.createWorkbook(file);
//
//			/* 创建sheet对象 */
//			WritableSheet ws = wwb.createSheet(fileName, 0);
//
//			/* 设定col列宽 */
//			setExcelColWith(ws, titleList);
//
//			/* 数据title写入 */
//			setExcelTitleData(ws, 0, titleList);
//
//			/* 数据写入 */
//			for (int iRow = 0; iRow < list.size(); iRow++) {
//				/* 信息取得 */
//				colList = (List)list.get(iRow);
//				/* 添加Label对象 */
//				setExcelData(ws, iRow + 1, colList);
//			}
//			/* 写入Excel工作薄 */
//			wwb.write();
//
//			/* 关闭Excel工作薄对象 */
//			wwb.close();
//			
//			/* 文件下载 */
//			fileUpload(fileName + ".xls", file, response);
//			
//		} catch (Exception e) {
//			e.printStackTrace();
//		} finally {
//			if (dao != null)
//				dao.disConnect();
//		}
//	}


	/**
	 * 文件输出
	 * 
	 * @param String fileName   文件信息名称(无扩展名)
	 * @param List title1List   一行标题(list中为Map,标题内容与合并单无格数[name,length])
	 * @param List title2List   二行标题(标题内容list)
	 * @param String sql   sql文

	 * @param HttpServletResponse response
	 */
	public static void export(String fileName, List title1List, List title2List, String sql, HttpServletResponse response)
			throws Exception {
		DbOperationExt dao = new DbOperationExt();
		List list = new ArrayList();
		List colList = new ArrayList();
		File file = null;
		try {
			/* 构建文件 */
			file = new File(fileName + ".xls");

			/* 指定的数据查询 */
			list = dao.queryData(sql);
			
			/* 构建Workbook对象 */
			WritableWorkbook wwb = Workbook.createWorkbook(file);

			/* 创建sheet对象 */
			WritableSheet ws = wwb.createSheet(fileName, 0);

			/* 设定col列宽 */
			setExcelColWith(ws, title2List);

			/* 数据title写入 */
			setExcelTitleData(ws, 0, title1List, title2List);

			/* 数据写入 */
			for (int iRow = 0; iRow < list.size(); iRow++) {
				/* 信息取得 */
				colList = (List)list.get(iRow);
				/* 添加Label对象 */
				setExcelData(ws, iRow + 2, colList);
			}
			/* 写入Excel工作薄 */
			wwb.write();

			/* 关闭Excel工作薄对象 */
			wwb.close();
			
			/* 文件下载 */
			fileUpload(fileName + ".xls", file, response);
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (dao != null)
				dao.disConnect();
		}
	}

//	/**
//	 * 文件输出
//	 * 
//	 * @param String fileName   文件信息名称(无扩展名)
//	 * @param List title1List   一行标题(list中为Map,标题内容与合并单无格数[name,length])
//	 * @param List title2List   二行标题(标题内容list)
//	 * @param String sql   sql文

//	 * @param HttpServletResponse response
//	 */
//	public static void exportDT(String fileName, List title1List, List title2List, List sql, HttpServletResponse response)
//			throws Exception {
//		DbOperationExt dao = new DbOperationExt();
//		List colList = new ArrayList();
//		File file = null;
//		try {
//			/* 构建文件 */
//			file = new File(fileName + ".xls");
//
//			/* 指定的数据查询 */
//			list = sql;
//			
//			/* 构建Workbook对象 */
//			WritableWorkbook wwb = Workbook.createWorkbook(file);
//
//			/* 创建sheet对象 */
//			WritableSheet ws = wwb.createSheet(fileName, 0);
//
//			/* 设定col列宽 */
//			setExcelColWith(ws, title2List);
//
//			/* 数据title写入 */
//			setExcelTitleData(ws, 0, title1List, title2List);
//
//			/* 数据写入 */
//			for (int iRow = 0; iRow < sql.size(); iRow++) {
//				/* 信息取得 */
//				colList = (List)sql.get(iRow);
//				/* 添加Label对象 */
//				setExcelData(ws, iRow + 2, colList);
//			}
//			/* 写入Excel工作薄 */
//			wwb.write();
//
//			/* 关闭Excel工作薄对象 */
//			wwb.close();
//			
//			/* 文件下载 */
//			fileUpload(fileName + ".xls", file, response);
//			
//		} catch (Exception e) {
//			e.printStackTrace();
//		} finally {
//			if (dao != null)
//				dao.disConnect();
//		}
//	}

	/**
	 * 下载文件
	 * 
	 * @param String fileName   文件信息名称(无扩展名)
	 * @param File file   文件
	 * @param HttpServletResponse response
	 * 
	 */
	public static void fileUpload(String fileName, File file,
			HttpServletResponse response) throws Exception {
		String filelength = String.valueOf(file.length());
		try {
			response.setContentType("application/x-download");
			response.setHeader("Location", fileName);
			response.setHeader("Content-Disposition", "attachment;filename=\""
					+ CommonTools.gBK2ISO_8859_1(fileName));

			response.setContentLength(Integer.parseInt(filelength));
			OutputStream outputStream = response.getOutputStream();
			InputStream inputStream = new FileInputStream(file);
			byte[] buffer = new byte[1024];
			int i = -1;
			while ((i = inputStream.read(buffer)) != -1) {
				outputStream.write(buffer, 0, i);
			}
			outputStream.flush();
			outputStream.close();
			inputStream.close();
			outputStream = null;

		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * Excel列宽设定
	 * @param List titleList   标题
	 * @param WritableSheet
	 *            ws
	 */
	private static void setExcelColWith(WritableSheet ws, List titleList)
			throws IOException {
		try {
			for (int i = 0; i < titleList.size(); i++) {
				ws.setColumnView(i, 15);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * Excel文件标题设定
	 * 
	 * @param WritableSheet
	 *            ws
	 * @param int
	 *            iRow
	 */
	private static void setExcelTitleData(WritableSheet ws, int iRow, List titleList)
			throws IOException {
		try {
			/* 宋体 12 非粗体 */
			WritableFont s12_b = new WritableFont(WritableFont
					.createFont("宋体"), 12, WritableFont.BOLD, false);
			WritableCellFormat f_s12_b = new WritableCellFormat(s12_b);
			f_s12_b.setBorder(Border.ALL, BorderLineStyle.THIN);
			f_s12_b.setAlignment(Alignment.CENTRE);
			
			/* 标题内容设定 */
			for (int i = 0; i < titleList.size(); i++) {
				Label lblZbbm = new Label(i, iRow, titleList.get(i).toString(), f_s12_b); // 各列内容
				ws.addCell(lblZbbm);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * Excel文件标题设定
	 * 
	 * @param WritableSheet
	 *            ws
	 * @param int
	 *            iRow
	 * @param List title1List   一行标题(list中为Map,标题内容与合并单无格数[name,length])
	 * @param List title2List   二行标题(标题内容list)
	 */
	private static void setExcelTitleData(WritableSheet ws, int iRow, List title1List, List title2List)
			throws IOException {
		Map map = null;
		String name = "";
		int length = 0;
		int iCol = 0;
		try {
			/* 宋体 12 非粗体 */
			WritableFont s12_b = new WritableFont(WritableFont
					.createFont("宋体"), 12, WritableFont.BOLD, false);
			WritableCellFormat f_s12_b = new WritableCellFormat(s12_b);
			f_s12_b.setBorder(Border.ALL, BorderLineStyle.THIN);
			f_s12_b.setAlignment(Alignment.CENTRE);
			
			/* 一行标题合并 与内容设定 */
			for (int i = 0; i < title1List.size(); i++) {
				map = (Map) title1List.get(i);
				name = map.get("name").toString();
				length = Integer.parseInt(map.get("length").toString());
				/* 内容设定 */
				Label lblZbbm = new Label(iCol, iRow, name, f_s12_b); 
				ws.addCell(lblZbbm);
				/* 合并 */
				ws.mergeCells(iCol, iRow, iCol + length - 1, iRow);
				iCol = iCol + length;
			}
			/* 二行标题内容设定 */
			for (int i = 0; i < title2List.size(); i++) {
				Label lblZbbm = new Label(i, iRow + 1, title2List.get(i).toString(), f_s12_b); 
				ws.addCell(lblZbbm);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	

	/**
	 * Excel文件内容设定
	 * 
	 * @param WritableSheet
	 *            ws
	 * @param int
	 *            iRow
	 * @param Map
	 *            zbInfo
	 */
	private static void setExcelData(WritableSheet ws, int iRow, Map dataInfo, List keyList) {
		try {
			/* 宋体 12 粗体 边线 */
			WritableFont s12_b = new WritableFont(
					WritableFont.createFont("宋体"), 12, WritableFont.BOLD, false);
			WritableCellFormat f_s12_b = new WritableCellFormat(s12_b);
			f_s12_b.setBorder(Border.ALL, BorderLineStyle.THIN);
			/* 宋体 12 非粗体 边线 */
			WritableFont s12_nb = new WritableFont(WritableFont
					.createFont("宋体"), 12, WritableFont.NO_BOLD, false);
			WritableCellFormat f_s12_nb = new WritableCellFormat(s12_nb);
			f_s12_nb.setBorder(Border.ALL, BorderLineStyle.THIN);

			for (int i = 0; i < keyList.size(); i++) {
				Label lblSjlx = new Label(i, iRow, dataInfo.get(
						keyList.get(i).toString()).toString(), f_s12_nb); // 数据
				ws.addCell(lblSjlx);
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * Excel文件内容设定
	 * 
	 * @param WritableSheet
	 *            ws
	 * @param int
	 *            iRow
	 * @param Map
	 *            zbInfo
	 */
	private static void setExcelData(WritableSheet ws, int iRow, List colList) {
		try {
			/* 宋体 12 粗体 边线 */
			WritableFont s12_b = new WritableFont(
					WritableFont.createFont("宋体"), 12, WritableFont.BOLD, false);
			WritableCellFormat f_s12_b = new WritableCellFormat(s12_b);
			f_s12_b.setBorder(Border.ALL, BorderLineStyle.THIN);
			/* 宋体 12 非粗体 边线 */
			WritableFont s12_nb = new WritableFont(WritableFont
					.createFont("宋体"), 12, WritableFont.NO_BOLD, false);
			WritableCellFormat f_s12_nb = new WritableCellFormat(s12_nb);
			f_s12_nb.setBorder(Border.ALL, BorderLineStyle.THIN);

			for (int i = 0; i < colList.size(); i++) {
				Label lblSjlx = new Label(i, iRow, colList.get(i).toString(), f_s12_nb); // 数据
				ws.addCell(lblSjlx);
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	
    /**Times New Roman
     * 设置excel字体及单元格样式
     * @param size 字体大小
     * @param style 单元格样式

     * @return
     */
    public static WritableCellFormat getNewRoman(int size,String style){
    	WritableFont wf = new WritableFont(WritableFont.createFont("Times New Roman"), size, WritableFont.NO_BOLD,false);
		WritableCellFormat wcfF = new WritableCellFormat(wf);
		try{
			wcfF.setAlignment(Alignment.RIGHT);                     /**把水平对齐方式指定为居右*/  
			wcfF.setVerticalAlignment(VerticalAlignment.CENTRE);    /**把垂直对齐方式指定为居中*/
//			wcfF.setBackground(Colour.WHITE);
			wcfF.setWrap(true);
			if("all".equals(style)){
				wcfF.setBorder(Border.TOP,BorderLineStyle.THIN);     /**把单元格上边框样式指定加重*/
				wcfF.setBorder(Border.LEFT,BorderLineStyle.THIN);     /**把单元格左边框样式指定加重*/
			    wcfF.setBorder(Border.RIGHT,BorderLineStyle.THIN);      /**把单元格右侧边框样式指定为加重*/
				wcfF.setBorder(Border.BOTTOM,BorderLineStyle.THIN);     /**把单元格底边框样式指定加重*/
			}else if("none".equals(style)){
				//单元格无边框
				wcfF.setBorder(Border.TOP,BorderLineStyle.NONE);     /**把单元格上边框样式指定虚线*/
				wcfF.setBorder(Border.LEFT,BorderLineStyle.NONE);     /**把单元格左边框样式指定虚线*/
			    wcfF.setBorder(Border.RIGHT,BorderLineStyle.NONE);      /**把单元格右侧边框样式指定为虚线*/
				wcfF.setBorder(Border.BOTTOM,BorderLineStyle.NONE);     /**把单元格底边框样式指定虚线*/
			}else if("center".equals(style)){
				wcfF.setAlignment(Alignment.CENTRE);                     /**把水平对齐方式指定为居右*/  
				//单元格有边框
				wcfF.setBorder(Border.TOP,BorderLineStyle.THIN);     /**把单元格上边框样式指定加重*/
				wcfF.setBorder(Border.LEFT,BorderLineStyle.THIN);     /**把单元格左边框样式指定加重*/
			    wcfF.setBorder(Border.RIGHT,BorderLineStyle.THIN);      /**把单元格右侧边框样式指定为加重*/
				wcfF.setBorder(Border.BOTTOM,BorderLineStyle.THIN);     /**把单元格底边框样式指定加重*/
			}
		}catch(Exception e){
			e.printStackTrace();
		}
    	
    	return wcfF;
    }
    
    /**宋体
     * 设置excel字体及单元格样式
     * @param size 字体大小
     * @param style 单元格样式

     * @return
     */
    public static WritableCellFormat getSongTi(int size,String style){
    	WritableFont wf = new WritableFont(WritableFont.createFont("Arial"),size, WritableFont.NO_BOLD,false);
		WritableCellFormat wcfF = new WritableCellFormat(wf);
		try{
			wcfF.setAlignment(Alignment.CENTRE);                     /**把水平对齐方式指定为居左*/  
			wcfF.setVerticalAlignment(VerticalAlignment.CENTRE);    /**把垂直对齐方式指定为居中*/
			wcfF.setBackground(Colour.WHITE);
			wcfF.setWrap(true);
			if("all".equals(style)){
				wcfF.setBorder(Border.TOP,BorderLineStyle.THIN);     /**把单元格上边框样式指定加重*/
				wcfF.setBorder(Border.LEFT,BorderLineStyle.THIN);     /**把单元格左边框样式指定加重*/
			    wcfF.setBorder(Border.RIGHT,BorderLineStyle.THIN);      /**把单元格右侧边框样式指定为加重*/
				wcfF.setBorder(Border.BOTTOM,BorderLineStyle.THIN);     /**把单元格底边框样式指定加重*/				
			}else if("none".equals(style)){
				//单元格无边框
				wcfF.setBorder(Border.TOP,BorderLineStyle.NONE);     /**把单元格上边框样式指定虚线*/
				wcfF.setBorder(Border.LEFT,BorderLineStyle.NONE);     /**把单元格左边框样式指定虚线*/
			    wcfF.setBorder(Border.RIGHT,BorderLineStyle.NONE);      /**把单元格右侧边框样式指定为虚线*/
				wcfF.setBorder(Border.BOTTOM,BorderLineStyle.NONE);     /**把单元格底边框样式指定虚线*/
			}else if("title".equals(style)){
				wcfF.setAlignment(Alignment.CENTRE);  					/**把水平对齐方式指定为居中*/  
				wcfF.setBorder(Border.TOP,BorderLineStyle.NONE);     /**把单元格上边框样式指定虚线*/
				wcfF.setBorder(Border.LEFT,BorderLineStyle.NONE);     /**把单元格左边框样式指定虚线*/
			    wcfF.setBorder(Border.RIGHT,BorderLineStyle.NONE);      /**把单元格右侧边框样式指定为虚线*/
				wcfF.setBorder(Border.BOTTOM,BorderLineStyle.NONE);     /**把单元格底边框样式指定虚线*/
			}
			
		}catch(Exception e){
			e.printStackTrace();
		}
    	
    	return wcfF;
    }
	public static void main(String[] args){}
	
    /**底色
     * 设置excel字体及单元格样式
     * @param size 字体大小
     * @param style 单元格样式

     * @return
     */
    public static WritableCellFormat getStyle(int size,String style){
    	WritableFont wf = new WritableFont(WritableFont.createFont("Arial"),size, WritableFont.NO_BOLD,false);
		WritableCellFormat wcfF = new WritableCellFormat(wf);
		try{
			wcfF.setAlignment(Alignment.CENTRE);                     /**把水平对齐方式指定为居左*/  
			wcfF.setVerticalAlignment(VerticalAlignment.CENTRE);    /**把垂直对齐方式指定为居中*/
			wcfF.setBackground(Colour.WHITE);
			wcfF.setWrap(true);
			if("0".equals(style)){
				wcfF.setBorder(Border.TOP,BorderLineStyle.THIN);     /**把单元格上边框样式指定加重*/
				wcfF.setBorder(Border.LEFT,BorderLineStyle.THIN);     /**把单元格左边框样式指定加重*/
			    wcfF.setBorder(Border.RIGHT,BorderLineStyle.THIN);      /**把单元格右侧边框样式指定为加重*/
				wcfF.setBorder(Border.BOTTOM,BorderLineStyle.THIN);     /**把单元格底边框样式指定加重*/
			}
			if("1".equals(style)){
				wcfF.setBorder(Border.TOP,BorderLineStyle.THIN);     /**把单元格上边框样式指定加重*/
				wcfF.setBorder(Border.LEFT,BorderLineStyle.THIN);     /**把单元格左边框样式指定加重*/
			    wcfF.setBorder(Border.RIGHT,BorderLineStyle.THIN);      /**把单元格右侧边框样式指定为加重*/
				wcfF.setBorder(Border.BOTTOM,BorderLineStyle.THIN);     /**把单元格底边框样式指定加重*/
				wcfF.setBackground(Colour.GRAY_25);
			}
			if("2".equals(style)){
				wcfF.setBorder(Border.TOP,BorderLineStyle.THIN);     /**把单元格上边框样式指定加重*/
				wcfF.setBorder(Border.LEFT,BorderLineStyle.THIN);     /**把单元格左边框样式指定加重*/
			    wcfF.setBorder(Border.RIGHT,BorderLineStyle.THIN);      /**把单元格右侧边框样式指定为加重*/
				wcfF.setBorder(Border.BOTTOM,BorderLineStyle.THIN);     /**把单元格底边框样式指定加重*/
				wcfF.setBackground(Colour.VERY_LIGHT_YELLOW);
			}
			if("3".equals(style)){
				wcfF.setBorder(Border.TOP,BorderLineStyle.THIN);     /**把单元格上边框样式指定加重*/
				wcfF.setBorder(Border.LEFT,BorderLineStyle.THIN);     /**把单元格左边框样式指定加重*/
			    wcfF.setBorder(Border.RIGHT,BorderLineStyle.THIN);      /**把单元格右侧边框样式指定为加重*/
				wcfF.setBorder(Border.BOTTOM,BorderLineStyle.THIN);     /**把单元格底边框样式指定加重*/
				wcfF.setBackground(Colour.PALE_BLUE);
			}
			if("4".equals(style)){
				wcfF.setBorder(Border.TOP,BorderLineStyle.THIN);     /**把单元格上边框样式指定加重*/
				wcfF.setBorder(Border.LEFT,BorderLineStyle.THIN);     /**把单元格左边框样式指定加重*/
			    wcfF.setBorder(Border.RIGHT,BorderLineStyle.THIN);      /**把单元格右侧边框样式指定为加重*/
				wcfF.setBorder(Border.BOTTOM,BorderLineStyle.THIN);     /**把单元格底边框样式指定加重*/
				wcfF.setBackground(Colour.LIGHT_GREEN);
			}
			if("5".equals(style)){
				wcfF.setBorder(Border.TOP,BorderLineStyle.THIN);     /**把单元格上边框样式指定加重*/
				wcfF.setBorder(Border.LEFT,BorderLineStyle.THIN);     /**把单元格左边框样式指定加重*/
			    wcfF.setBorder(Border.RIGHT,BorderLineStyle.THIN);      /**把单元格右侧边框样式指定为加重*/
				wcfF.setBorder(Border.BOTTOM,BorderLineStyle.THIN);     /**把单元格底边框样式指定加重*/
				wcfF.setBackground(Colour.LIGHT_GREEN);
			}
			if("6".equals(style)){
				wcfF.setBorder(Border.TOP,BorderLineStyle.THIN);     /**把单元格上边框样式指定加重*/
				wcfF.setBorder(Border.LEFT,BorderLineStyle.THIN);     /**把单元格左边框样式指定加重*/
			    wcfF.setBorder(Border.RIGHT,BorderLineStyle.THIN);      /**把单元格右侧边框样式指定为加重*/
				wcfF.setBorder(Border.BOTTOM,BorderLineStyle.THIN);     /**把单元格底边框样式指定加重*/
				wcfF.setBackground(Colour.LAVENDER);
			}
			if("7".equals(style)){
				wcfF.setBorder(Border.TOP,BorderLineStyle.THIN);     /**把单元格上边框样式指定加重*/
				wcfF.setBorder(Border.LEFT,BorderLineStyle.THIN);     /**把单元格左边框样式指定加重*/
			    wcfF.setBorder(Border.RIGHT,BorderLineStyle.THIN);      /**把单元格右侧边框样式指定为加重*/
				wcfF.setBorder(Border.BOTTOM,BorderLineStyle.THIN);     /**把单元格底边框样式指定加重*/
				wcfF.setBackground(Colour.ROSE);
			}
			if("8".equals(style)){
				wcfF.setBorder(Border.TOP,BorderLineStyle.THIN);     /**把单元格上边框样式指定加重*/
				wcfF.setBorder(Border.LEFT,BorderLineStyle.THIN);     /**把单元格左边框样式指定加重*/
			    wcfF.setBorder(Border.RIGHT,BorderLineStyle.THIN);      /**把单元格右侧边框样式指定为加重*/
				wcfF.setBorder(Border.BOTTOM,BorderLineStyle.THIN);     /**把单元格底边框样式指定加重*/
				wcfF.setBackground(Colour.VERY_LIGHT_YELLOW);
			}
			if("9".equals(style)){
				wcfF.setBorder(Border.TOP,BorderLineStyle.THIN);     /**把单元格上边框样式指定加重*/
				wcfF.setBorder(Border.LEFT,BorderLineStyle.THIN);     /**把单元格左边框样式指定加重*/
			    wcfF.setBorder(Border.RIGHT,BorderLineStyle.THIN);      /**把单元格右侧边框样式指定为加重*/
				wcfF.setBorder(Border.BOTTOM,BorderLineStyle.THIN);     /**把单元格底边框样式指定加重*/
				wcfF.setBackground(Colour.YELLOW);
			}
			if("10".equals(style)){
				wcfF.setBorder(Border.TOP,BorderLineStyle.THIN);     /**把单元格上边框样式指定加重*/
				wcfF.setBorder(Border.LEFT,BorderLineStyle.THIN);     /**把单元格左边框样式指定加重*/
			    wcfF.setBorder(Border.RIGHT,BorderLineStyle.THIN);      /**把单元格右侧边框样式指定为加重*/
				wcfF.setBorder(Border.BOTTOM,BorderLineStyle.THIN);     /**把单元格底边框样式指定加重*/
				wcfF.setBackground(Colour.LAVENDER);
			}
		}catch(Exception e){
			e.printStackTrace();
		}
    	
    	return wcfF;
    }
    
    /**公式
     * @return
     */
    public static WritableCellFormat getExpressions(int size,String style){
    	WritableFont wf = new WritableFont(WritableFont.createFont("Arial"),size, WritableFont.NO_BOLD,false);
		WritableCellFormat wcfF = new WritableCellFormat(wf);

		try{
			wcfF.setAlignment(Alignment.CENTRE);                     /**把水平对齐方式指定为居左*/  
			wcfF.setVerticalAlignment(VerticalAlignment.CENTRE);    /**把垂直对齐方式指定为居中*/
			wcfF.setBackground(Colour.WHITE);
			wcfF.setWrap(true);   
			if("all".equals(style)){
				wcfF.setBorder(Border.TOP,BorderLineStyle.THIN);     /**把单元格上边框样式指定加重*/
				wcfF.setBorder(Border.LEFT,BorderLineStyle.THIN);     /**把单元格左边框样式指定加重*/
			    wcfF.setBorder(Border.RIGHT,BorderLineStyle.THIN);      /**把单元格右侧边框样式指定为加重*/
				wcfF.setBorder(Border.BOTTOM,BorderLineStyle.THIN);     /**把单元格底边框样式指定加重*/
				wcfF.setBackground(Colour.ROSE);
			}
			
		}catch(Exception e){
			e.printStackTrace();
		}
    	
    	return wcfF;
    }
}
